/*****************************************************************************
 *
 * HUSONG PROPRIETARY INFORMATION
 *
 * The information contained herein is proprietary to HUSONG
 * and shall not be reproduced or disclosed in whole or in part
 * or used for any design or manufacture
 * without direct written authorization from HUSONG.
 *
 * Copyright (c) 2015 by HUSONG. All rights reserved.
 *
 *****************************************************************************/
package me.send.business.service.mapper;

import java.util.List;
import java.util.Map;

import me.send.business.service.bean.TaskBean;
import me.send.business.service.bean.base.Page;
import me.send.business.service.pojo.Data;
import me.send.business.service.pojo.OrderInfoBean;
import me.send.business.service.pojo.OrderMap;
import me.send.business.service.pojo.User;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author maxmin
 *
 * @date 2015年8月4日 下午2:52:00
 */
public abstract interface OrderMapper {
    @Select(value = "SELECT count(1) from orderbroker where broker_id=#{popid} AND order_id=#{orderid}")
    /**
     * 判断一个经纪人是否抢过该单
     * @param params
     * @return
     */
    public abstract Integer getOrderCountByBrokerId(
	    Map<String, Object> params);
    
    /**
     * 获取该经纪人已完成的订单
     * @param params
     * @return
     */
    @Select(value = "SELECT COUNT(1),p.`level` FROM orders o LEFT JOIN orderbroker ob ON o.orderid = ob.order_id "
    		+ "LEFT JOIN popuser p ON p.popid = #{brokerid} "
    		+ "WHERE ob.ischecked = 1 AND ob.broker_id=#{brokerid} AND o.`status`>7 LIMIT 1")
    public abstract Map<String, Object> getOrderCountAndLevelById(int brokerid);
    
    @Insert(value = "INSERT INTO orderbroker (createtime,updatetime,broker_id,order_id,longitude,latitude) VALUES (#{createtime},#{updatetime},#{popid},#{orderid},#{longitude},#{latitude})")
    /** 抢单
     * @param popid
     * @param orderid
     */
    public abstract int addCompetBroker(Map<String, Object> params);
    
    @Update(value = "UPDATE orders SET status = #{status} WHERE orderid=#{orderid} AND status=#{preStatus}")
    public abstract int updateCompetStatus(Map<String, Object> params);
    
    @Update(value = "UPDATE orders SET status = #{status},brokerid=#{popid},fee=#{fee} WHERE orderid=#{orderid} AND status=#{preStatus}")
    /** 经纪人修改已接单订单状态
     * @param popid
     * @param orderid
     */
    public abstract int updateStatus(Map<String, Object> params);
    
    /**
     * 修改指定订单的状态
     * @param orderid
     */
    @Update("update orders set status=#{status} where orderid=#{orderid}")
    public abstract void updateOrderStatus(Map<String, Object> param);
    
    @Select(value = "select isvAccountId from user as u,orders as o where u.userid=o.userid and orderid = #{orderid}")
    public abstract String getUserByOrderId(int orderid);
    
    /**
     * 显示所有为接的订单
     * 
     * @param page
     * @return
     */
    @Select(value = "select o.orderid orderid,o.getaddress,o.sendaddress,o.address,o.longitude,o.latitude,o.voicepath,o.type,o.fee,o.tip,o.status,o.ordertypeid,o.descption,user.userid,nickname,headerpath,(SELECT group_concat(orderpic.picpath) picpath FROM orderpic WHERE orderpic.orderid=o.orderid) picpath "
		    + "from orders o "
		    + "LEFT JOIN orderstatus os ON o.status = os.orderstatusid  "
		    + "LEFT JOIN user ON o.userid = user.userid "
		    + "where o.orderid>#{startid} "
		    + "and orderid not in (select order_id from orderbroker where broker_id=#{popid}) "
		    + "and (o.status=1 or o.status=2) "
		    + "order by orderid desc " + "limit #{size}")
    public abstract List<OrderMap> home_refresh(Page p);
    
    @Select(value = "select o.orderid orderid,o.getaddress,o.sendaddress,o.address,o.longitude,o.latitude,o.voicepath,o.type,o.fee,o.tip,o.status,o.ordertypeid,o.descption,user.userid,nickname,headerpath,(SELECT group_concat(orderpic.picpath) picpath FROM orderpic WHERE orderpic.orderid=o.orderid) picpath "
		    + "from orders o "
		    + "LEFT JOIN orderstatus os ON o.status = os.orderstatusid  "
		    + "LEFT JOIN user ON o.userid = user.userid "
		    + "where o.orderid<#{startid} "
		    + "and orderid not in (select order_id from orderbroker where broker_id=#{popid}) "
		    + "and (o.status=1 or o.status=2) "
		    + "order by orderid desc " + "limit #{size}")
    public abstract List<OrderMap> home_load(Page p);
    
    @Select(value = "select o.orderid orderid,o.getaddress,o.sendaddress,o.address,o.longitude,o.latitude,o.voicepath,o.type,o.fee,o.tip,o.status,o.ordertypeid,o.descption,user.userid,nickname,headerpath,(SELECT group_concat(orderpic.picpath) picpath FROM orderpic WHERE orderpic.orderid=o.orderid) picpath "
		    + "from orders o "
		    + "LEFT JOIN orderstatus os ON o.status = os.orderstatusid  "
		    + "LEFT JOIN ordertype ot ON o.type = ot.ordertypeid "
		    + "LEFT JOIN user ON o.userid = user.userid "
		    + "where o.orderid<#{startid} "
		    + "and o.ordertypeid=#{type} "
		    + "and orderid not in (select order_id from orderbroker where broker_id=#{popid}) "
		    + "and (o.status=1 or o.status=2) "
		    + "order by orderid desc " + "limit #{size}")
    public abstract List<OrderMap> home1_load(Map<String, Integer> m);
    
    @Select(value = "select o.orderid orderid,o.getaddress,o.sendaddress,o.address,o.longitude,o.latitude,o.voicepath,o.type,o.fee,o.tip,o.status,o.ordertypeid,o.descption,user.userid,nickname,headerpath,(SELECT group_concat(orderpic.picpath) picpath FROM orderpic WHERE orderpic.orderid=o.orderid) picpath "
		    + "from orders o "
		    + "LEFT JOIN orderstatus os ON o.status = os.orderstatusid  "
		    + "LEFT JOIN ordertype ot ON o.type = ot.ordertypeid "
		    + "LEFT JOIN user ON o.userid = user.userid "
		    + "where o.orderid>#{startid} "
		    + "and o.ordertypeid=#{type} "
		    + "and (o.status=1 or o.status=2) "
		    + "and orderid not in (select order_id from orderbroker where broker_id=#{popid}) "
		    + "order by orderid desc " + "limit #{size}")
    public abstract List<OrderMap> home1_refresh(Map<String, Integer> m);
    
    @Select(value = "select count(*)/#{size}+1 pagenum,count(*) numtotal from orders,orderstatus "
		    + "where orders.status=orderstatus.orderstatusid  and (orders.status=1 or orders.status=2) "
		    + "and orders.orderid not in (select order_id from orderbroker where broker_id=#{popid}) ")
    public abstract Data count_page(Page page);
    
    @Select(value = "select count(*)/#{size}+1 pagenum,count(*) numtotal from orders,orderstatus "
		    + "where orders.status=orderstatus.orderstatusid  and (orders.status=1 or orders.status=2) "
		    + "and orders.ordertypeid=#{type} "
		    + "and orders.orderid not in (select order_id from orderbroker where broker_id=#{popid}) ")
    public abstract Data count_page1(Map<String, Integer> m);
    
    /**
     * 获取订单详情
     * @param order
     * @return
     */
    @Select(value = "SELECT o.orderid orderid,o.createtime,o.voicepath,o.type,o.fee,o.tip,o.`status`,o.ordertypeid,`user`.nickname username,`user`.telephone,o.address,o.descption,o.ordertypeid,o.type,cd.text fromAddress,cd1.text toAddress,cd.`name` fromName,"
		    + "cd1.`name` toName, cd.telephone fromTelephone,cd1.telephone toTelephone,group_concat(op.picpath) picpath "
		    + "FROM orders o LEFT JOIN orderpic op ON op.orderid = o.orderid LEFT JOIN `user` ON `user`.userid = o.userid LEFT JOIN commaddress cd ON cd.addressid = o.address_id LEFT JOIN commaddress cd1 "
		    + "ON cd1.addressid = o.to_address_id WHERE o.orderid = #{orderid} LIMIT 0,1")
    public abstract OrderInfoBean getOrder(int orderid);
    
    /**
     * 获取经纪人任务列表
     * @param brokerid
     * @return
     */
    @Select(value = "SELECT o.ordertypeid,o.`status`,o.voicepath,o.sendaddress,o.descption,o.getaddress,o.fee,o.type,o.tip,o.orderid,o.createtime,ob.order_broker_id orderbrokerid,u.telephone FROM orderbroker ob LEFT JOIN orders o ON ob.order_id = o.orderid LEFT JOIN `user` u ON o.userid = u.userid WHERE (IF ((ob.ischecked = 0 AND ob.broker_id=#{brokerid} AND o.`status`<3),1=1,1=2) OR IF ((o.`status`>2 AND ob.ischecked = 1 AND ob.broker_id=#{brokerid}),1=1,1=2)) AND o.`status`>=2 AND IF (#{startid}=0,1=1,ob.order_broker_id<#{startid}) ORDER BY ob.order_broker_id DESC limit #{size}")
    public abstract List<TaskBean> taskList(Map<String, Object> params);
    
    @Select(value = "SELECT o.ordertypeid,o.`status`,o.voicepath,o.sendaddress,o.descption,o.getaddress,o.fee,o.type,o.tip,o.orderid,o.createtime,ob.order_broker_id orderbrokerid,u.telephone "
		    + "FROM orderbroker ob LEFT JOIN orders o ON ob.order_id = o.orderid "
		    + "LEFT JOIN `user` u ON o.userid = u.userid WHERE ob.ischecked = 1 AND IF(#{startid}=0,1=1,o.orderid<#{startid}) AND o.`status` = 8 AND ob.broker_id = #{brokerid} order by o.orderid desc LIMIT #{size}")
    public abstract List<TaskBean> completedRecord(
	    Map<String, Object> params);
    
    /**
     * 根据订单号获取订单的详细信息
     * @param orderid
     * @return
     */
    @Select("select * from orders where orderid=#{orderid}")
    public abstract Map<String, Object> getOrderFeeInfo(int orderid);
    
    @Select(value="SELECT username,nickname,headerpath,sex,telephone,u.userid,isvAccountId FROM `user` u LEFT JOIN orders o ON u.userid = o.userid WHERE o.orderid = #{orderid}")
    public abstract User getUserInfoByOrderId(int orderid);
}
